import json
import os
from typing import Optional, Dict, Any

import pandas as pd
from openpyxl.reader.excel import load_workbook


def readFile(url):
    # read() - 读取所有
    try:
        with open(url, 'r', encoding='utf-8') as file:
            json_data = json.load(file)
    except FileNotFoundError:
        print("错误: 文件未找到!")
        # pass
    finally:
        file.close()
    return json_data


if __name__ == '__main__':
    # 获取当前脚本所在目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    parent_dir = os.path.dirname(current_dir)
    # 构建目标文件路径
    target_file = os.path.join(parent_dir, 'data', 'json_data.json')

    # 读取文件内容
    json_data = readFile(target_file)

    # 解析json数据
    work_order_nos = []
    bill_nos = []
    json_array = []
    for hit in json_data["hits"]["hits"]:
        source = hit["_source"]
        if "work_order_no" in source:
            work_order_nos.append(source["work_order_no"])
        if "bill_no" in source:
            bill_nos.extend(source["bill_no"])
        # 定义一个字典来保存json数组数据
        customInfo: Optional[Dict[str, Any]] = source.get("custom_info")
        entry = {
            "work_order_no": source.get("work_order_no"),
            "bill_no": source.get("bill_no"),
            "trouble_type_code": source.get("trouble_type_code"),
            "member_id": customInfo.get("memberId") if customInfo else None
        }
        json_array.append(entry)

    print("所有的 work_order_no:", work_order_nos)
    print("所有的 bill_no:", bill_nos)
    print("json数组数据:", json_array)

    # 创建 DataFrame
    df = pd.DataFrame(json_array)
    # 将 bill_no 列中的列表转换为字符串
    df['bill_no'] = df['bill_no'].apply(lambda x: ', '.join(map(str, x)))
    # 导出到 Excel 文件
    df.to_excel('output.xlsx', index=False)

    # 使用 openpyxl 加载工作簿
    wb = load_workbook('output.xlsx')
    ws = wb.active

    # 指定统一的列宽
    column_width = 20  # 这里设置统一列宽为 20
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        ws.column_dimensions[column_letter].width = column_width

    # 保存修改后的工作簿
    wb.save('output.xlsx')
